﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spCompressDatabase]
#-- Purpose:		Compresses the data in a database (DATA_COMPRESSION=PAGE)
#--	Last Update:	12/14/2011
#--					For a complete history - please review comments in Version
#--					control.
#-- Called By:		Scheduled Job
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spCompressDatabase]
(
	@database_name			sysname,
	@min_rows				int				= 100000,
	@print_query			bit				= 0,
	@execute_query			bit				= 1
)
AS

SET NOCOUNT ON

DECLARE @sSQL nvarchar(max)

SELECT  @sSQL = '
USE ' + @database_name + ';

--- Declare Local Variables
DECLARE @tPage TABLE (object_name sysname, schema_name sysname, index_id int, partition_number int, size_with_current_compression_setting bigint, size_with_requested_compression_setting bigint, sample_size_with_current_compression_setting bigint, sample_size_with_requested_compression_setting bigint)
DECLARE @index sysname, @table sysname, @schema sysname, @sSQL nvarchar(max)

--- Determine what to Compress
DECLARE crs CURSOR FOR
	SELECT		sc.name schema_name,
				ta.name table_name
	FROM		sys.tables ta
	JOIN		sys.partitions pa ON pa.OBJECT_ID = ta.OBJECT_ID
	JOIN		sys.schemas sc ON ta.schema_id = sc.schema_id
	WHERE		ta.is_ms_shipped = 0 AND pa.index_id IN (1,0)
	GROUP BY	sc.name,
				ta.name
	HAVING		SUM(pa.rows) > ' + CAST(@min_rows as nvarchar(20)) + '
OPEN crs
FETCH NEXT FROM crs INTO @schema, @table
WHILE @@FETCH_STATUS <> -1
  BEGIN
	BEGIN TRY
		INSERT INTO @tPage
			exec sp_estimate_data_compression_savings @schema, @table, NULL, NULL, ''PAGE''
	END TRY
	BEGIN CATCH
	END CATCH

	FETCH NEXT FROM crs INTO @schema, @table
  END
CLOSE crs
DEALLOCATE crs

--- Perform Index Compression
DECLARE crsRebuild CURSOR FOR
	SELECT		ind.name index_name,
				tp.object_name,
				tp.schema_name
	FROM		@tPage tp
	JOIN		sys.indexes ind ON ind.index_id = tp.index_id
				AND ind.object_id = OBJECT_ID(DB_NAME() + ''.'' + schema_name + ''.'' + object_name)
	ORDER BY	((CAST(size_with_current_compression_setting as float)-size_with_requested_compression_setting) / size_with_current_compression_setting) ASC
OPEN crsRebuild
FETCH NEXT FROM crsRebuild INTO @index, @table, @schema
WHILE @@FETCH_STATUS <> -1
  BEGIN
	SELECT  @sSQL = ''ALTER INDEX '' + QUOTENAME(@index) + '' ON '' + QUOTENAME(@schema) + ''.'' + QUOTENAME(@table) + '' REBUILD WITH ( DATA_COMPRESSION = PAGE );''
	EXEC sp_executesql @sSQL
	FETCH NEXT FROM crsRebuild INTO @index, @table, @schema
  END
CLOSE crsRebuild
DEALLOCATE crsRebuild'


--- Print the Query
IF ISNULL(@print_query, 0) = 1
	PRINT	@sSQL

--- Run the Query
IF ISNULL(@execute_query, 1) = 1
	EXEC	sp_executesql @sSQL

SET NOCOUNT OFF